package com.huabo.contract.mysql.mapper;


import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblOrganizationMySql;
import com.huabo.contract.mysql.entity.TblStaffMySql;

import java.math.BigDecimal;

public class TblStaffMapperSqlMySqlConifg {

    public String selectListByPageInfo(PageInfo<TblStaffMySql> pageInfo, BigDecimal orgid, TblOrganizationMySql organization) {

        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,CAST(@ROW := @ROW + 1 AS SIGNED) AS RN FROM (select org.* from TBL_ORGANIZATION org where ORG.FATHERORGID = getparentHyorgList(#{orgid})  CONNECT BY PRIOR ORG.ORGID = ORG.FATHERORGID");
        if (organization.getOrgtype() != null && organization.getOrgtype().toString().equals("0")) {
            sqlSb.append("select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID =" + organization.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        sqlSb.append(" order by STAFFID desc) BUDGET limit " + pageInfo.getCurrentRecord() + " , " + pageInfo.getPageSize() + " ) as a");
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectListByPageInfoCount(BigDecimal orgid, TblOrganizationMySql organization) {

        StringBuffer sqlSb = new StringBuffer("select count(*) from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID=" + organization.getOrgid() + " AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        if (organization.getOrgtype() != null && organization.getOrgtype().toString().equals("0")) {
            sqlSb.append("select COUNT(*) from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID =" + organization.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectListByPage(PageInfo<TblStaffMySql> pageInfo, BigDecimal orgid, TblOrganizationMySql organization) {

        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,CAST(@ROW := @ROW + 1 AS SIGNED) AS RN FROM (select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from tbl_staff sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID=" + organization.getOrgid() + " AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        if (organization.getOrgtype() != null && organization.getOrgtype().toString().equals("0")) {
            sqlSb.append("select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID =" + organization.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        sqlSb.append(" order by STAFFID desc) BUDGET LIMIT " + pageInfo.getCurrentRecord() + " , " + pageInfo.getPageSize() + " ) as a");
        String sql = sqlSb.toString();
        return sql;
    }

    public String findByRolelOne(String rolename, String orgid) {
        String sql = "SELECT *,@ROW := @ROW + 1 AS SIGNED FROM(SELECT @ROW := 0) RTBL_STAFF  WHERE ROLEID in (SELECT RID FROM TBL_ROLE where rname='" + rolename + "科长' or rname='" + rolename + "副局长' or rname='" + rolename + "处长'  or rname='" + rolename + "局长')  AND ORGID in (select ORGID from TBL_ORGANIZATION where 1=1 AND ORGTYPE=0 start with  FATHERORGID=" + orgid + " connect by prior FATHERORGID= ORGID) HAVING SIGNED = 1";
        return sql.toString();
    }

    public String findByStaffManOrgs(String orgid) {
        String sql = "SELECT * FROM TBL_STAFF WHERE MANAGEORGS like '%" + orgid + "%'";
        return sql;
    }

    public String findByJobName(String jobname, String orgid) {
        String sql = "SELECT * FROM TBL_STAFF WHERE ORGID=" + orgid + " AND JOBID in (SELECT JOBID from TBL_JOB WHERE COMPANYID in (select orgid from TBL_ORGANIZATION o where o.ORGTYPE != 0  START WITH o.ORGID = " + orgid + " CONNECT by PRIOR o.FATHERORGID = o.ORGID) AND JOBNAME like'%" + jobname + "%')";
        return sql.toString();
    }

    public String findAllPageBeanPid(PageInfo<TblStaffMySql> pageInfo, TblOrganizationMySql attribute) {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,CAST(@ROW := @ROW + 1 AS SIGNED) AS RN FROM (select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from TBL_STAFF STA INNER JOIN TBL_ORGANIZATION ORG ON STA.ORGID=ORG.ORGID WHERE 1=1 ");
        if (attribute.getOrgtype() != null && attribute.getOrgtype().toString().equals("0")) {
            sqlSb.append("AND STA.ORGID =" + attribute.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        } else {
            sqlSb.append("AND STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID=" + attribute.getOrgid() + " AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }

        sqlSb.append(" order by STAFFID desc) BUDGET LIMIT " + pageInfo.getCurrentRecord() + " , " + pageInfo.getPageSize() + " ) as a");
        String sql = sqlSb.toString();
        return sql.toString();
    }

    public String findAllCountPageBeanPid(PageInfo<TblStaffMySql> pageInfo, TblOrganizationMySql attribute) {
        StringBuffer sqlSb = new StringBuffer("select COUNT(*) from TBL_STAFF STA INNER JOIN TBL_ORGANIZATION ORG ON STA.ORGID=ORG.ORGID WHERE 1=1 ");
        if (attribute.getOrgtype() != null && attribute.getOrgtype().toString().equals("0")) {
            sqlSb.append(" AND STA.ORGID =" + attribute.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        } else {
            sqlSb.append(" AND STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID=" + attribute.getOrgid() + " AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        return sqlSb.toString();
    }


    public String updateTs(TblStaffMySql ts) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_STAFF SET USERNAME = " + ts.getUsername() + " ");

        if (ts.getRealname() != null && !"".equals(ts.getRealname())) {
            sql.append(" , REALNAME = '" + ts.getRealname() + "'");
        }
        if (ts.getFixedphone() != null && !"".equals(ts.getFixedphone())) {
            sql.append(" , FIXEDPHONE = '" + ts.getFixedphone() + "'");
        }
        if (ts.getAddress() != null && !"".equals(ts.getAddress())) {
            sql.append(" , ADDRESS = '" + ts.getAddress() + "'");
        }
        if (ts.getEmail() != null && !"".equals(ts.getEmail())) {
            sql.append(" , EMAIL = '" + ts.getEmail() + "'");
        }
        if (ts.getMiblephone() != null && !"".equals(ts.getMiblephone())) {
            sql.append(" , MIBLEPHONE = '" + ts.getMiblephone() + "'");
        }
        if (ts.getMemo() != null && !"".equals(ts.getMemo())) {
            sql.append(" , MEMO = '" + ts.getMemo() + "'");
        }
        if (ts.getPassword() != null && !"".equals(ts.getPassword())) {
            sql.append(" , PASSWORD = '" + ts.getPassword() + "'");
        }
        if (ts.getJobid() != null && !"".equals(ts.getJobid())) {
            sql.append(" , JOBID = '" + ts.getJobid() + "'");
        }
        if (ts.getCreatetime() != null && !"".equals(ts.getCreatetime())) {
            sql.append(" ,CREATETIME = '" + DateUtil.parseDate(ts.getCreatetime(), "yyyy-MM-dd HH:mm:ss") + "'");
        }
        if (ts.getStatus() != null && !"".equals(ts.getStatus())) {
            sql.append(" , STATUS = '" + ts.getStatus() + "'");
        }

        sql.append(" WHERE staffid = '" + ts.getStaffid() + "'");
        return sql.toString();
    }


    public String saveTs(TblStaffMySql ts) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_STAFF (STAFFID");
        StringBuffer value = new StringBuffer(" VALUES (" + ts.getStaffid() + "");

        if (ts.getRealname() != null && !"".equals(ts.getRealname())) {
            column.append(",REALNAME");
            value.append(",'" + ts.getRealname() + "'");
        }
        if (ts.getFixedphone() != null && !"".equals(ts.getFixedphone())) {
            column.append(",FIXEDPHONE");
            value.append(",'" + ts.getFixedphone() + "'");
        }
        if (ts.getAddress() != null && !"".equals(ts.getAddress())) {
            column.append(",ADDRESS");
            value.append(",'" + ts.getAddress() + "'");
        }
        if (ts.getEmail() != null && !"".equals(ts.getEmail())) {
            column.append(",EMAIL");
            value.append(",'" + ts.getEmail() + "'");
        }
        if (ts.getMiblephone() != null && !"".equals(ts.getMiblephone())) {
            column.append(",MIBLEPHONE");
            value.append(",'" + ts.getMiblephone() + "'");
        }
        if (ts.getMemo() != null && !"".equals(ts.getMemo())) {
            column.append(",MEMO");
            value.append(",'" + ts.getMemo() + "'");
        }
        if (ts.getUsername() != null && !"".equals(ts.getUsername())) {
            column.append(",USERNAME");
            value.append(",'" + ts.getUsername() + "'");
        }
        if (ts.getPassword() != null && !"".equals(ts.getPassword())) {
            column.append(",PASSWORD");
            value.append(",'" + ts.getPassword() + "'");
        }
        if (ts.getJobid() != null && !"".equals(ts.getJobid())) {
            column.append(",JOBID");
            value.append(",'" + ts.getJobid() + "'");
        }
        if (ts.getCreatetime() != null && !"".equals(ts.getCreatetime())) {
            column.append(",CREATETIME");
            value.append(",'" + DateUtil.parseDate(ts.getCreatetime(), "yyyy-MM-dd HH:mm:ss") + "'");
        }
        if (ts.getStatus() != null && !"".equals(ts.getStatus())) {
            column.append(",STATUS");
            value.append(",'" + ts.getStatus() + "'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString() + value.toString();
        return sql;
    }

    //==
    public String selectTreeListByPageInfo(PageInfo<TblStaffMySql> pageInfo, Integer orgid) {
        StringBuffer sb = new StringBuffer("SELECT * FROM "
                + "(SELECT T1.*,CAST(@ROW := @ROW + 1 AS SIGNED) AS RN  FROM "
                + "(SELECT TNA.*,ORG.ORGNAME "
                + "FROM TBL_STAFF TNA "
                + "LEFT JOIN TBL_ORGANIZATION ORG ON ORG.ORGID = TNA.ORGID "
                + "WHERE 1=1 ");
        if (null != orgid) {
            sb.append(" AND TNA.ORGID = " + orgid);
        }

        sb.append(" ORDER BY TNA.STAFFID DESC) T1 LIMIT " + pageInfo.getCurrentRecord() + " , " + pageInfo.getPageSize() + " ) as a");
        return sb.toString();
    }

    public String selectTreeCountByPageInfo(PageInfo<TblStaffMySql> pageInfo, Integer orgid) {
        StringBuffer sb = new StringBuffer("SELECT COUNT(0) "
                + "FROM TBL_STAFF TNA "
                + " LEFT JOIN TBL_ORGANIZATION ORG ON ORG.ORGID = TNA.ORGID "
                + "WHERE 1=1 ");

        if (null != orgid) {
            sb.append(" AND TNA.ORGID = " + orgid);
        }

        return sb.toString();
    }
}
